﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using System.Collections;
using SCRUM.Core.DAO;

namespace SCRUM.Core.DataProviders
{
    using SCRUM.Core.Exceptions.Database;
    using System.Data.SqlClient;

    /// <summary>
    /// Provider for comunication with the database
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {
        /// <summary>
        /// Gets all teams.
        /// </summary>
        /// <returns>List of all teams.</returns>
        public override List<Team> GetAllTeams()
        {
            List<Team> output = new List<Team>();

            try
            {
                connection.Open();
                DbDataReader data = GetGrid("team/GetAllTeams");

                while (data.Read())
                {
                    Team team = new Team(
                        (int)data["ID"],
                        (string)data["NAME"]);
                    output.Add(team);
                }
            }
            finally
            {
                connection.Close();
            }
            return output;
        }

        /// <summary>
        /// Gets all teams.
        /// </summary>
        /// <param name="project">Associated project</param>
        /// <returns>List of teams associated to the project.</returns>
        public override IList<Team> GetAllTeams(int projectID)
        {
            IList<Team> output = new List<Team>();

            DbParameter projID = CreateParameter("projectID", projectID);
            try
            {
                connection.Open();
                DbDataReader data = GetGrid("team/GetAllTeamsInProject", projID);

                while (data.Read())
                {
                    Team team = new Team(
                        (int)data["ID"],
                        (string)data["NAME"]);
                    output.Add(team);
                }
            }
            finally
            {
                connection.Close();
            }
            return output;
        }

        /// <summary>
        /// Gets the team.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <returns>Team with specified id.</returns>
        public override Team GetTeam(int id)
        {
            Team output = null;
            DbParameter teamID = CreateParameter("teamID", id);
            try
            {
                connection.Open();
                DbDataReader data = GetGrid("team/GetTeamByID", teamID);

                if (data.Read())
                {
                    output = new Team(
                        (int)data["ID"],
                        (string)data["NAME"]);
                }               
            }
            finally
            {
                connection.Close();
            }
            return output;
        }

        /// <summary>
        /// Gets the team.
        /// </summary>
        /// <param name="user">The user of the team.</param>
        /// <returns>Team for specified user.</returns>
        public override IList<Team> GetTeamForUser(User user)
        {
            IList<Team> teams = new List<Team>();

            
            try
            {
                DbParameter userID = CreateParameter("userID", user.ID);
                connection.Open();
                DbDataReader data = GetGrid("team/GetTeamByUser", userID);

                while (data.Read())
                {
                    Team team = new Team(
                        (int)data["ID"],
                        (string)data["NAME"]
                    );

                    teams.Add(team);
                }
            }
            finally
            {
                connection.Close();
            }
            return teams;
        }

        /// <summary>
        /// Creates new team in specified project with specified team name
        /// </summary>
        /// <param name="teamName">Name of new team.</param>
        /// <param name="projectID">new team's project ID.</param>
        public override Team CreateTeam(string teamName, int projectID)
        {
            try
            {
                DbParameter dbName = CreateParameter("name", teamName);
                DbParameter dbProjectID = CreateParameter("projectID", projectID);
                connection.Open();
                int result = ExecuteNonQuery("team/CreateTeam", dbName, dbProjectID);
                if (result == 0)
                {
                    throw new InconsistentDatabaseException("TeamNotCreated");
                }
                int newTeamID = Convert.ToInt32(GetScalar("team/GetLastInsertedTeamID"));
                return new Team(newTeamID, teamName);
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("TeamDupliciteName");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the team with specified ID.
        /// </summary>
        /// <param name="teamID">The team ID.</param>
        public override void DeleteTeam(int teamID)
        {
            try
            {
                DbParameter dbTeamID = CreateParameter("teamID", teamID);
                connection.Open();
                int result = ExecuteNonQuery("team/DeleteTeam", dbTeamID);
                if (result == 0)
                {
                    throw new InconsistentDatabaseException("TeamToDeleteNotFound");
                }
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("TeamNotDeleted");
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
